My Excel workbook has a sheet for each month named January, February and so on. I also have a summary sheet that displays key calculations for a particular month. For example, one of my formulas is: =SUM(February!F1:F10). Is there any way that I can enter the month name into a cell on my summary sheet, and then have my formulas use the data for that specified worksheet?
Yes. Excel's indirect function was designed specifically for this sort of thing. This function accepts a text string as an argument, and then evaluates the text string to arrive at a cell or range reference. In your case, assume that cell B1 on your summary worksheet holds the month name. The following formula utilises the indirect function to create the range reference used by the SUM function:
=SUM(INDIRECT(B1&"!F1:F10"))
Note that we use the ampersand operator to join the month name with the cell reference (expressed as text).
|
Introduction | Applications
